﻿/******************************************************************************
'Description:工时报表类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2018.6.22
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
namespace ReportBusiness
{
    public class uMESWorkTimeReportBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();
        private uMESCommonBusiness common = new uMESCommonBusiness();

        #region JJFC--工时报表查询  add by zhunan on 2018.6.22

        // 查询工时报表  左侧内容 add by zhunan on 2018.6.22
        public DataTable GetEmployeeWorkTime(Dictionary<string, object> para)
        {

            string CurrentWorkTimeStartDate = "";// 本月工时 开始时间
            string CurrentWorkTimeEndDate = "";  // 本月工时 截止时间

            int worktimeday = Convert.ToInt32(para["worktimeday"]);

            if (DateTime.Now.Day > worktimeday)
            {
                // 当天大于工时节点，本月工时开始时间取本月工时节点, 本月工时截止时间取当天
                CurrentWorkTimeStartDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, worktimeday).ToShortDateString();
                CurrentWorkTimeEndDate = DateTime.Now.ToShortDateString();
            }
            else
            {
                if (DateTime.Now.Month != 1)
                {
                    // 当天不大于工时节点，当前月份不为1月时，本月工时开始时间取上个月工时节点，本月工时截止时间取当天
                    CurrentWorkTimeStartDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month - 1, worktimeday).ToShortDateString();
                    CurrentWorkTimeEndDate = DateTime.Now.ToShortDateString();
                }
                else
                {
                    // 当天不大于工时节点，当前月份为1月时，本月工时开始时间取上一年12工时节点，本月工时截止时间取当天
                    CurrentWorkTimeStartDate = new DateTime(DateTime.Now.Year - 1, 12, worktimeday).ToShortDateString();
                    CurrentWorkTimeEndDate = DateTime.Now.ToShortDateString();
                }
            }


            //查询个人工时（个人工时会随所选的检验时间不同会发生变更）
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT t.employeeid,t.employeename,t.fullname,NVL(SUM(t.employeeWorktime),0) employeeWorktime");
            sb.AppendLine("FROM (");
            sb.AppendLine("      SELECT NVL(wts.workerid,e.employeeid) employeeid,NVL(e2.fullname,e.fullname) fullname, NVL(e2.employeename,e.employeename) employeename,");
            sb.AppendLine("             (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN wts.splitduration  ");
            sb.AppendLine("                   WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN wt.totalduration");
            sb.AppendLine("                   WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END");
            sb.AppendLine("             ) employeeWorktime");
            sb.AppendLine("      FROM containersteptimesheet wt");
            sb.AppendLine("      LEFT JOIN employee e ON e.employeeid = wt.workerid");
            sb.AppendLine("      LEFT JOIN container c ON wt.containerid = c.containerid");
            sb.AppendLine("      LEFT JOIN product p ON p.productid = c.productid");
            sb.AppendLine("      LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            sb.AppendLine("      LEFT JOIN factory f ON F.factoryid = wt.factoryid");
            sb.AppendLine("      LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid");
            sb.AppendLine("      LEFT JOIN Team t ON t.teamid = wt.teamid");
            sb.AppendLine("      LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid");
            sb.AppendLine("      LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid");
            sb.AppendLine("      LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid"); // add zhunan 2018.4.9
            sb.AppendLine("      LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid");// add zhunan 2018.6.8 查询分配工时
            sb.AppendLine("      LEFT JOIN employee e2 ON e2.employeeid = wts.workerid");
            sb.AppendLine("      WHERE wt.workerid IS NOT NULL AND c.containerid<>c.containername AND c.parentcontainerid IS NULL");
            #region 查询条件
            // 产品过滤
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND pb.productname = '{0}'", para["productname"]);
                }
            }
            // 产品型号过滤
            if (para["productfamilyname"].ToString() != "全部")
            {
                if (para["productfamilyname"].ToString().Equals("WP13汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP13%' OR  c.mfgbatch like '%全通 %' OR c.mfgbatch like '%13通 %'");
                    sb.AppendFormat("       or c.mfgbatch like '%WP13AII%' OR  c.mfgbatch like '%WPFI%' OR c.mfgbatch like '%WPFII%')");
                }
                else if (para["productfamilyname"].ToString().Equals("WP7汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP7%'  OR c.mfgbatch like '%7通 %')");
                }
                else
                {
                    sb.AppendFormat(" and c.mfgbatch like '%{0}%' ", para["productfamilyname"] + " ");
                }

            }
            // 工序过滤
            if (para.ContainsKey("workflowstepname"))
            {
                if (para["workflowstepname"].ToString() != "全部")
                {
                    sb.AppendFormat(" and ws.workflowstepname like '%{0}%' ", para["workflowstepname"]);
                }
            }
            // 检验开始时间
            if (para.ContainsKey("CheckDataStart"))
            {
                if (para["CheckDataStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["CheckDataStart"]);
                }
            }
            // 检验结束时间
            if (para.ContainsKey("CheckDataEnd"))
            {
                if (para["CheckDataEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["CheckDataEnd"]);
                }
            }
            // 员工过滤
            if (para.ContainsKey("employeename"))
            {
                if (para["employeename"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND NVL(e2.fullname,e.fullname) ='{0}' ", para["employeename"].ToString());
                }
            }
            // 车间过滤
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname='{0}' ", para["factoryname"].ToString());
                }
            }
            // 工区过滤
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername='{0}' ", para["workcentername"]);
                }
            }
            // 班组过滤
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname='{0}' ", para["teamname"]);
                }
            }
            // 状态过滤
            if (para["state"].ToString() != "全部")
            {
                //state状态含义： 0-->未提交，1-->已提交，3-->已核算，4-->已核销
                if (para["state"].ToString().Equals("0"))
                {
                    sb.Append(" AND wt.submitdate IS NULL ");
                }
                if (para["state"].ToString().Equals("1"))
                {
                    sb.Append(" AND wt.submitdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("3"))
                {
                    sb.Append(" AND wt.adjustdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("4"))
                {
                    sb.Append(" AND wt.verificationdate IS NOT NULL ");
                }
            }
            // 工时提交开始时间
            if (para.ContainsKey("WorktimeSubmiteDateStart"))
            {
                if (para["WorktimeSubmiteDateStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateStart"]);
                }
            }
            // 工时提交结束时间
            if (para.ContainsKey("WorktimeSubmiteDateEnd"))
            {
                if (para["WorktimeSubmiteDateEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateEnd"]);
                }
            }
            #endregion
            sb.AppendLine("");
            sb.AppendLine(") t");
            sb.AppendLine("GROUP BY t.employeeid,t.employeename,t.fullname");
            sb.AppendLine("ORDER BY t.employeename");

            DataTable dtQueryData = dal.GetDataTable(sb.ToString());

            //查当月工时（当月工时是固定值）
            sb = new StringBuilder();
            sb.AppendLine("SELECT t.employeeid,t.employeename,t.fullname,NVL(SUM(t.employeeWorktime),0) employeeCurrentMonthWorkTime");
            sb.AppendLine("FROM (");
            sb.AppendLine("      SELECT NVL(wts.workerid,e.employeeid) employeeid,NVL(e2.fullname,e.fullname) fullname, NVL(e2.employeename,e.employeename) employeename,");
            sb.AppendLine("             (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN wts.splitduration  ");
            sb.AppendLine("                   WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN wt.totalduration");
            sb.AppendLine("                   WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END");
            sb.AppendLine("             ) employeeWorktime");
            sb.AppendLine("      FROM containersteptimesheet wt");
            sb.AppendLine("      LEFT JOIN employee e ON e.employeeid = wt.workerid");
            sb.AppendLine("      LEFT JOIN container c ON wt.containerid = c.containerid");
            sb.AppendLine("      LEFT JOIN product p ON p.productid = c.productid");
            sb.AppendLine("      LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            sb.AppendLine("      LEFT JOIN factory f ON F.factoryid = wt.factoryid");
            sb.AppendLine("      LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid");
            sb.AppendLine("      LEFT JOIN Team t ON t.teamid = wt.teamid");
            sb.AppendLine("      LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid");
            sb.AppendLine("      LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid");
            sb.AppendLine("      LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid"); // add zhunan 2018.4.9
            sb.AppendLine("      LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid");// add zhunan 2018.6.8 查询分配工时
            sb.AppendLine("      LEFT JOIN employee e2 ON e2.employeeid = wts.workerid");
            sb.AppendLine("      WHERE wt.workerid IS NOT NULL AND c.containerid<>c.containername AND c.parentcontainerid IS NULL");
            #region 查询条件
            // 产品过滤
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND pb.productname = '{0}'", para["productname"]);
                }
            }
            // 产品型号过滤
            if (para["productfamilyname"].ToString() != "全部")
            {
                if (para["productfamilyname"].ToString().Equals("WP13汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP13%' OR  c.mfgbatch like '%全通 %' OR c.mfgbatch like '%13通 %'");
                    sb.AppendFormat("       or c.mfgbatch like '%WP13AII%' OR  c.mfgbatch like '%WPFI%' OR c.mfgbatch like '%WPFII%')");
                }
                else if (para["productfamilyname"].ToString().Equals("WP7汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP7%'  OR c.mfgbatch like '%7通 %')");
                }
                else
                {
                    sb.AppendFormat(" and c.mfgbatch like '%{0}%' ", para["productfamilyname"] + " ");
                }

            }
            // 工序过滤
            if (para.ContainsKey("workflowstepname"))
            {
                if (para["workflowstepname"].ToString() != "全部")
                {
                    sb.AppendFormat(" and ws.workflowstepname like '%{0}%' ", para["workflowstepname"]);
                }
            }
            // 检验开始时间
            sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", CurrentWorkTimeStartDate);
            // 检验结束时间
            sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", CurrentWorkTimeEndDate);
            // 员工过滤
            if (para.ContainsKey("employeename"))
            {
                if (para["employeename"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND NVL(e2.fullname,e.fullname) ='{0}' ", para["employeename"].ToString());
                }
            }
            // 车间过滤
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname='{0}' ", para["factoryname"].ToString());
                }
            }
            // 工区过滤
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername='{0}' ", para["workcentername"]);
                }
            }
            // 班组过滤
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname='{0}' ", para["teamname"]);
                }
            }
            // 状态过滤
            if (para["state"].ToString() != "全部")
            {
                //state状态含义： 0-->未提交，1-->已提交，3-->已核算，4-->已核销
                if (para["state"].ToString().Equals("0"))
                {
                    sb.Append(" AND wt.submitdate IS NULL ");
                }
                if (para["state"].ToString().Equals("1"))
                {
                    sb.Append(" AND wt.submitdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("3"))
                {
                    sb.Append(" AND wt.adjustdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("4"))
                {
                    sb.Append(" AND wt.verificationdate IS NOT NULL ");
                }
            }
            // 工时提交开始时间
            if (para.ContainsKey("WorktimeSubmiteDateStart"))
            {
                if (para["WorktimeSubmiteDateStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateStart"]);
                }
            }
            // 工时提交结束时间
            if (para.ContainsKey("WorktimeSubmiteDateEnd"))
            {
                if (para["WorktimeSubmiteDateEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateEnd"]);
                }
            }
            #endregion
            sb.AppendLine("");
            sb.AppendLine(") t");
            sb.AppendLine("GROUP BY t.employeeid,t.employeename,t.fullname");
            sb.AppendLine("ORDER BY t.employeename");

            DataTable dtCurrentMonthWorkTime = dal.GetDataTable(sb.ToString());

            dtQueryData.Columns.Add("employeeCurrentMonthWorkTime");//当月工时

            for (int i = 0; i < dtQueryData.Rows.Count; i++)
            {
                double employeeWorktimeMinutes = Convert.ToDouble(dtQueryData.Rows[i]["employeeWorktime"].ToString());
                // 将个人工时分钟数替换成小时
                dtQueryData.Rows[i]["employeeWorktime"] = (employeeWorktimeMinutes / 60).ToString("0.00");

                for (int j = 0; j < dtCurrentMonthWorkTime.Rows.Count; j++)
                {
                    if (dtCurrentMonthWorkTime.Rows[j]["employeeid"].ToString() == dtQueryData.Rows[i]["employeeid"].ToString())
                    {
                        double employeeCurrentMonthWorkTimeMinutes = Convert.ToDouble(dtCurrentMonthWorkTime.Rows[j]["employeeCurrentMonthWorkTime"].ToString());
                        dtQueryData.Rows[i]["employeeCurrentMonthWorkTime"] = (employeeCurrentMonthWorkTimeMinutes / 60).ToString("0.00");
                    }
                }
            }

            return dtQueryData;
        }

        // 查询各分厂当月工时统计节点 add zhunan 2018.9.17
        public int GetMonthWorkTimeDay(string factoryname)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT ut.attributevalue FROM factory f");
            sb.AppendLine("LEFT JOIN Userattribute ut ON ut.parentid = f.factoryid AND ut.userattributename='月度工时统计节点'");
            sb.AppendFormat("WHERE ut.attributevalue IS NOT NULL AND f.factoryname = '{0}'", factoryname);

            DataTable dt = dal.GetDataTable(sb.ToString());

            int worktimeday = 19;
            if (dt.Rows.Count != 0)
            {
                worktimeday = Convert.ToInt32(dt.Rows[0]["attributevalue"]);
            }
            return worktimeday;
        }

        public int GetMonthWorkTimeDay(Dictionary<string,object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT ut.attributevalue FROM factory f");
            sb.AppendLine("LEFT JOIN Userattribute ut ON ut.parentid = f.factoryid AND ut.userattributename='月度工时统计节点'");
            sb.AppendFormat("WHERE ut.attributevalue IS NOT NULL AND f.factoryid = '{0}'", para["factoryid"].ToString());

            DataTable dt = dal.GetDataTable(sb.ToString());

            int worktimeday = 19;
            if (dt.Rows.Count != 0)
            {
                worktimeday = Convert.ToInt32(dt.Rows[0]["attributevalue"]);
            }
            return worktimeday;
        }

        // 员工工时--明细信息  右侧 modify zhunan on 2018.6.22
        public DataTable GetEmployeeWorkTimeDetails(Dictionary<string, object> para, out int pagecount, out int rowCount)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT mfg.mfgordername,c.containername,ws.workflowstepname specname,(wt.qty-wt.scrapqty) qty,w.workcentername,wt.createdate reporttime,NVL(cki.createdate,cfc.createdate) checkDate,");
            sb.AppendLine("       NVL(e2.fullname,e.fullname) fullname,NVL(e2.employeename,e.employeename) employeename,");
            sb.AppendLine("       NVL(e2.employeeid,e.employeeid) employeeid,f.factoryname,pb.productname,t.teamname,wt.submitdate sumitdate,nvl(wt.durationperunit,sd.durationperunit) durationperunit,");
            sb.AppendLine("       (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN wts.splitduration ");
            sb.AppendLine("             WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN wt.totalduration");
            sb.AppendLine("             WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END");
            sb.AppendLine("        ) sumworktime,wt.submitdate,wt.adjustdate,wt.verificationdate");
            sb.AppendLine("FROM containersteptimesheet wt");
            sb.AppendLine("LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid AND cki.isuse = 0"); // modify zhunan 2018.8.28 --AND cki.isuse = 0
            sb.AppendLine("LEFT JOIN employee e ON e.employeeid = wt.workerid");
            sb.AppendLine("LEFT JOIN container c ON wt.containerid = c.containerid");
            sb.AppendLine("LEFT JOIN mfgorder mfg ON mfg.mfgorderid = c.mfgorderid");
            sb.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            sb.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            sb.AppendLine("LEFT JOIN factory f ON F.factoryid = wt.factoryid");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid");
            sb.AppendLine("LEFT JOIN Team t ON t.teamid = wt.teamid");
            sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid");
            sb.AppendLine("LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid");
            sb.AppendLine("LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid");
            sb.AppendLine("LEFT JOIN employee e2 ON e2.employeeid = wts.workerid");
            sb.AppendLine("LEFT JOIN containerfinishedcheckinfo cfc ON wt.containercheckinfoid = cfc.containerfinishedcheckinfoid AND cfc.isuse = 0"); // add zhunan 2018.8.28
            sb.AppendLine("WHERE wt.workerid IS NOT NULL AND c.containerid<>c.containername AND c.parentcontainerid IS NULL");
            #region 查询条件
            // 产品过滤
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND pb.productname = '{0}'", para["productname"]);
                }
            }
            // 产品型号过滤
            if (para["productfamilyname"].ToString() != "全部")
            {
                if (para["productfamilyname"].ToString().Equals("WP13汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP13%' OR  c.mfgbatch like '%全通 %' OR c.mfgbatch like '%13通 %'");
                    sb.AppendFormat("       or c.mfgbatch like '%WP13AII%' OR  c.mfgbatch like '%WPFI%' OR c.mfgbatch like '%WPFII%')");
                }
                else if (para["productfamilyname"].ToString().Equals("WP7汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP7%'  OR c.mfgbatch like '%7通 %')");
                }
                else
                {
                    sb.AppendFormat(" and c.mfgbatch like '%{0}%' ", para["productfamilyname"] + " ");
                }

            }
            // 工序过滤
            if (para.ContainsKey("workflowstepname"))
            {
                if (para["workflowstepname"].ToString() != "全部")
                {
                    sb.AppendFormat(" and ws.workflowstepname like '%{0}%' ", para["workflowstepname"]);
                }
            }
            // 检验开始时间
            if (para.ContainsKey("CheckDataStart"))
            {
                if (para["CheckDataStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["CheckDataStart"]);
                }
            }
            // 检验结束时间
            if (para.ContainsKey("CheckDataEnd"))
            {
                if (para["CheckDataEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["CheckDataEnd"]);
                }
            }
            // 员工过滤
            if (para.ContainsKey("employeename"))
            {
                if (para["employeename"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND NVL(e2.fullname,e.fullname) ='{0}' ", para["employeename"].ToString());
                }
            }
            // 车间过滤
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname='{0}' ", para["factoryname"].ToString());
                }
            }
            // 工区过滤
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername='{0}' ", para["workcentername"]);
                }
            }
            // 班组过滤
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname='{0}' ", para["teamname"]);
                }
            }
            // 状态过滤
            if (para["state"].ToString() != "全部")
            {
                //state状态含义： 0-->未提交，1-->已提交，3-->已核算，4-->已核销
                if (para["state"].ToString().Equals("0"))
                {
                    sb.Append(" AND wt.submitdate IS NULL ");
                }
                if (para["state"].ToString().Equals("1"))
                {
                    sb.Append(" AND wt.submitdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("3"))
                {
                    sb.Append(" AND wt.adjustdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("4"))
                {
                    sb.Append(" AND wt.verificationdate IS NOT NULL ");
                }
            }
            // 工时提交开始时间
            if (para.ContainsKey("WorktimeSubmiteDateStart"))
            {
                if (para["WorktimeSubmiteDateStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateStart"]);
                }
            }
            // 工时提交结束时间
            if (para.ContainsKey("WorktimeSubmiteDateEnd"))
            {
                if (para["WorktimeSubmiteDateEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateEnd"]);
                }
            }
            if (para.ContainsKey("employeeid"))
            {
                if (para["employeeid"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(e2.employeeid,e.employeeid) = '{0}'", para["employeeid"]);
                }
            }
            #endregion
            sb.AppendLine("ORDER by e.fullname,c.containername,ws.workflowstepname ");

            uMESPagingDataDTO retRt = dal.GetPagingDataIns(sb.ToString(), int.Parse(para["intPage"].ToString()), int.Parse(para["pageSize"].ToString()));
            pagecount = Convert.ToInt32(retRt.PageCount);
            rowCount = Convert.ToInt32(retRt.RowCount);

            DataTable dtResult = retRt.DBTable;

            dtResult.Columns.Add("isSubmit");// 是否已提交
            dtResult.Columns.Add("isHeSuan");// 是否已核算
            dtResult.Columns.Add("isHeXiao");// 是否已核销

            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                // submitdate字段为空时代表未提交
                if (!string.IsNullOrEmpty(dtResult.Rows[i]["submitdate"].ToString()))
                {
                    dtResult.Rows[i]["isSubmit"] = "已提交";
                }
                else
                {
                    dtResult.Rows[i]["isSubmit"] = "未提交";
                }

                // adjustdate字段为空代表未核算
                if (!string.IsNullOrEmpty(dtResult.Rows[i]["adjustdate"].ToString()))
                {
                    dtResult.Rows[i]["isHeSuan"] = "已核算";
                }
                else
                {
                    dtResult.Rows[i]["isHeSuan"] = "未核算";
                }

                // verificationdate字段为空时代表未核销
                if (!string.IsNullOrEmpty(dtResult.Rows[i]["verificationdate"].ToString()))
                {
                    dtResult.Rows[i]["isHeXiao"] = "已核销";
                }
                else
                {
                    dtResult.Rows[i]["isHeXiao"] = "未核销";
                }

                //dtResult.Rows[i]["specname"] = common.GetSpecNameWithOutProdName(dtResult.Rows[i]["specname"].ToString());
            }
            return dtResult;
        }

        #endregion

        #region JJFC--工时报表导出 modify zhunan on 2018.6.22
        public DataTable ExportEmployeeWorkTime(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT mfg.mfgordername,c.containername,ws.workflowstepname specname, si.Description stepinfodesc,(wt.qty-wt.scrapqty) qty,w.workcentername,wt.createdate reporttime,NVL(cki.createdate,cfc.createdate) checkDate,");
            sb.AppendLine("       NVL(e2.fullname,e.fullname) fullname,NVL(e2.employeename,e.employeename) employeename,");
            sb.AppendLine("       NVL(e2.employeeid,e.employeeid) employeeid,f.factoryname,pb.productname,t.teamname,wt.submitdate sumitdate,nvl(wt.durationperunit,sd.durationperunit) durationperunit,");
            sb.AppendLine("       (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN wts.splitduration ");
            sb.AppendLine("             WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN wt.totalduration");
            sb.AppendLine("             WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END");
            sb.AppendLine("        ) sumworktime,wt.submitdate,wt.adjustdate,wt.verificationdate");
            sb.AppendLine("FROM containersteptimesheet wt");
            sb.AppendLine("LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid AND cki.isuse = 0 --工序检"); // modify zhunan 2018.8.28 --AND cki.isuse = 0
            sb.AppendLine("LEFT JOIN employee e ON e.employeeid = wt.workerid");
            sb.AppendLine("LEFT JOIN container c ON wt.containerid = c.containerid");
            sb.AppendLine("LEFT JOIN mfgorder mfg ON mfg.mfgorderid = c.mfgorderid");
            sb.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            sb.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            sb.AppendLine("LEFT JOIN factory f ON F.factoryid = wt.factoryid");
            sb.AppendLine("LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid");
            sb.AppendLine("LEFT JOIN Team t ON t.teamid = wt.teamid");
            sb.AppendLine("LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid");
            sb.AppendLine("LEFT JOIN stepinfo SI ON si.Stepinfoid=cki.stepinfoid ");
            sb.AppendLine("LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid");
            sb.AppendLine("LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid");
            sb.AppendLine("LEFT JOIN employee e2 ON e2.employeeid = wts.workerid");
            sb.AppendLine("LEFT JOIN containerfinishedcheckinfo cfc ON wt.containercheckinfoid = cfc.containerfinishedcheckinfoid AND cfc.isuse = 0 --工序成品检"); // add zhunan 2018.8.28
            sb.AppendLine("WHERE wt.workerid IS NOT NULL AND c.containerid<>c.containername AND c.parentcontainerid IS NULL");
            #region 查询条件
            // 产品过滤
            if (para.ContainsKey("productname"))
            {
                if (para["productname"].ToString() != "")
                {
                    sb.AppendFormat(" AND pb.productname = '{0}'", para["productname"]);
                }
            }
            // 产品型号过滤
            if (para["productfamilyname"].ToString() != "全部")
            {
                if (para["productfamilyname"].ToString().Equals("WP13汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP13%' OR  c.mfgbatch like '%全通 %' OR c.mfgbatch like '%13通 %'");
                    sb.AppendFormat("       or c.mfgbatch like '%WP13AII%' OR  c.mfgbatch like '%WPFI%' OR c.mfgbatch like '%WPFII%')");
                }
                else if (para["productfamilyname"].ToString().Equals("WP7汇总"))
                {
                    sb.AppendFormat(" and (c.mfgbatch like '%WP7%'  OR c.mfgbatch like '%7通 %')");
                }
                else
                {
                    sb.AppendFormat(" and c.mfgbatch like '%{0}%' ", para["productfamilyname"] + " ");
                }

            }
            // 工序过滤
            if (para.ContainsKey("workflowstepname"))
            {
                if (para["workflowstepname"].ToString() != "全部")
                {
                    sb.AppendFormat(" and ws.workflowstepname like '%{0}%' ", para["workflowstepname"]);
                }
            }
            // 检验开始时间
            if (para.ContainsKey("CheckDataStart"))
            {
                if (para["CheckDataStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["CheckDataStart"]);
                }
            }
            // 检验结束时间
            if (para.ContainsKey("CheckDataEnd"))
            {
                if (para["CheckDataEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND NVL(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["CheckDataEnd"]);
                }
            }
            // 员工过滤
            if (para.ContainsKey("employeename"))
            {
                if (para["employeename"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND NVL(e2.fullname,e.fullname)='{0}' ", para["employeename"].ToString());
                }
            }
            // 车间过滤
            if (para.ContainsKey("factoryname"))
            {
                if (para["factoryname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND f.factoryname='{0}' ", para["factoryname"].ToString());
                }
            }
            // 工区过滤
            if (para.ContainsKey("workcentername"))
            {
                if (para["workcentername"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND w.workcentername='{0}' ", para["workcentername"]);
                }
            }
            // 班组过滤
            if (para.ContainsKey("teamname"))
            {
                if (para["teamname"].ToString() != "全部")
                {
                    sb.AppendFormat(" AND t.teamname='{0}' ", para["teamname"]);
                }
            }
            // 状态过滤
            if (para["state"].ToString() != "全部")
            {
                //state状态含义： 0-->未提交，1-->已提交，3-->已核算，4-->已核销
                if (para["state"].ToString().Equals("0"))
                {
                    sb.Append(" AND wt.submitdate IS NULL ");
                }
                if (para["state"].ToString().Equals("1"))
                {
                    sb.Append(" AND wt.submitdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("3"))
                {
                    sb.Append(" AND wt.adjustdate IS NOT NULL ");
                }
                if (para["state"].ToString().Equals("4"))
                {
                    sb.Append(" AND wt.verificationdate IS NOT NULL ");
                }
            }
            // 工时提交开始时间
            if (para.ContainsKey("WorktimeSubmiteDateStart"))
            {
                if (para["WorktimeSubmiteDateStart"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateStart"]);
                }
            }
            // 工时提交结束时间
            if (para.ContainsKey("WorktimeSubmiteDateEnd"))
            {
                if (para["WorktimeSubmiteDateEnd"].ToString() != "")
                {
                    sb.AppendFormat(" AND wt.submitdate <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["WorktimeSubmiteDateEnd"]);
                }
            }
            #endregion
            sb.AppendLine("ORDER BY e.fullname,c.containername,ws.workflowstepname ");

            DataTable dtResult = dal.GetDataTable(sb.ToString());

            dtResult.Columns.Add("isSubmit");// 是否已提交
            dtResult.Columns.Add("isHeSuan");// 是否已核算
            dtResult.Columns.Add("isHeXiao");// 是否已核销

            for (int i = 0; i < dtResult.Rows.Count; i++)
            {
                // submitdate字段为空时代表未提交
                if (!string.IsNullOrEmpty(dtResult.Rows[i]["submitdate"].ToString()))
                {
                    dtResult.Rows[i]["isSubmit"] = "已提交";
                }
                else
                {
                    dtResult.Rows[i]["isSubmit"] = "未提交";
                }

                // adjustdate字段为空代表未核算
                if (!string.IsNullOrEmpty(dtResult.Rows[i]["adjustdate"].ToString()))
                {
                    dtResult.Rows[i]["isHeSuan"] = "已核算";
                }
                else
                {
                    dtResult.Rows[i]["isHeSuan"] = "未核算";
                }

                // verificationdate字段为空时代表未核销
                if (!string.IsNullOrEmpty(dtResult.Rows[i]["verificationdate"].ToString()))
                {
                    dtResult.Rows[i]["isHeXiao"] = "已核销";
                }
                else
                {
                    dtResult.Rows[i]["isHeXiao"] = "未核销";
                }
            }
            return dtResult;
        }
        #endregion

        //批产、施工单明细查询sql
        public string GetEmpWorkTimeSql(Dictionary<string, object> para)
        {
            string sql = @" SELECT NVL(e2.employeeid,e.employeeid) employeeid,NVL(e2.employeename,e.employeename) employeename,NVL(e2.fullname,e.fullname) fullname,
                                   c.togetherflag containerno,ws.workflowstepname specname,ws.description wsdescription,si.Description stepinfodesc,w.workcentername,
                                   wt.createdate reporttime,cki.createdate checkDate,
                                   CASE WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='1')
                                        THEN nvl(wt.durationperunit,si.durationperunit)
                                        WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='2')
                                        THEN nvl(wt.durationperunit,si.durationperunit2)
                                   ELSE NVL(wt.durationperunit,sd.durationperunit) END durationperunit,
                                   CASE WHEN wt.stepinfoid IS NOT NULL AND csw.checktype ='1'
                                        THEN coalesce(wts.SPLITDURATION,wt.totalduration,round(SI.DURATIONPERUNIT / NVL(wt.SPLITCOUNT, 1), 2), 0) 
                                        WHEN wt.stepinfoid IS NOT NULL AND csw.checktype ='2'
                                        THEN coalesce(wts.SPLITDURATION,wt.totalduration, round(SI.Durationperunit2 / NVL(wt.SPLITCOUNT, 1), 2), 0)  
                                        ELSE coalesce(wt.totalduration, wt.durationperunit, wts.splitduration, round(sd.DURATIONPERUNIT / NVL(wt.SPLITCOUNT, 1), 2), 0) 
                                    END employeeworktime,decode(csw.checktype,'2','检装','初装') checktype,
                                    wfb.workflowname || ':' || wf.workflowrevision workflowname,
                                    wf.description workflowdesc
                            FROM containersteptimesheet wt
                            LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid AND cki.isuse = 0
                            LEFT JOIN employee e ON e.employeeid = wt.workerid
                            LEFT JOIN container c ON wt.containerid = c.containerid
                            LEFT JOIN mfgorder mfg ON mfg.mfgorderid = c.mfgorderid
                            LEFT JOIN product p ON p.productid = c.productid
                            LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                            LEFT JOIN factory f ON F.factoryid = wt.factoryid
                            LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid
                            LEFT JOIN Team t ON t.teamid = wt.teamid
                            LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid
                            LEFT JOIN stepinfo SI ON si.Stepinfoid=cki.stepinfoid
                            LEFT JOIN containerstepworkreport csw ON cki.containerstepworkreportid=csw.containerstepworkreportid
                            LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
                            LEFT JOIN workflowbase wfb ON wf.workflowbaseid = wfb.workflowbaseid
                            LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
                            LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
                            LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
                            LEFT JOIN userattribute ua ON ua.parentid=c.mfgorderid 
                            LEFT JOIN enginepartplan epl ON NVL(epl.enginecontainerid,epl.containerid) = c.containerid 
                            WHERE wt.workerid IS NOT NULL AND c.status='1'  AND ua.userattributename='发动机' AND c.parentcontainerid IS NULL ";
            #region 查询条件
            // 产品过滤
            if (para.ContainsKey("productname"))
            {
                sql += string.Format(" AND pb.productname = '{0}'", para["productname"].ToString());
            }

            // 发动机过滤
            if (para.ContainsKey("engineno"))
            {
                sql += string.Format(" AND ua.attributevalue LIKE '%{0}%'", para["engineno"].ToString());
            }

            // 检验开始时间
            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND nvl(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["startDate"].ToString());
            }

            // 检验结束时间
            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND  nvl(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["endDate"].ToString());
            }
            // 员工过滤
            if (para.ContainsKey("employeeid"))
            {
                sql += string.Format(" AND NVL(e2.employeeid,e.employeeid) ='{0}' ", para["employeeid"].ToString());
            }

            // 车间过滤
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND f.factoryid='{0}' ", para["factoryid"].ToString());
            }

            // 工区过滤
            if (para.ContainsKey("workcenterid"))
            {
                sql += string.Format(" AND w.workcenterid='{0}' ", para["workcenterid"].ToString());
            }

            // 班组过滤
            if (para.ContainsKey("teamid"))
            {
                sql += string.Format(" AND t.teamid='{0}' ", para["teamid"].ToString());
            }

            if (para.ContainsKey("ddlType"))
            {
                if (para["ddlType"].ToString() == "批产工时")
                {
                    sql += string.Format(" and epl.workflowstepid is not null "); //批产任务工时
                }
                if (para["ddlType"].ToString() == "施工工时")
                {
                    sql += string.Format(" and epl.workflowstepid is null ");
                }
            }

            if (para.ContainsKey("status"))
            {
                if (para["status"].ToString() == "已核算")
                {
                    sql += string.Format(" AND wt.AdjustDate IS NOT NULL ");
                }
                else if (para["status"].ToString() == "已核销")
                {
                    sql += string.Format(" AND wt.VerificationDate IS NOT NULL ");
                }
                else
                {
                    sql += string.Format(" AND NVL(wt.adjustdate,wt.verificationdate) IS NULL ");//未处理
                }
            }
            #endregion

            sql += string.Format(" ORDER by e.fullname,c.containername,ws.workflowstepname ");

            return sql;
        }

        //批产、施工工时汇总
        public DataTable GetEmployeeWorkTimeZS(Dictionary<string, object> para)
        {
            string sql = @" SELECT t.employeeid,t.employeename,t.fullname,SUM(t.employeeWorktime) employeeWorktime
                            FROM 
                            ( 
                            SELECT NVL(e2.employeeid,e.employeeid) employeeid,NVL(e2.fullname,e.fullname) fullname,NVL(e2.employeename,e.employeename) employeename,
                                    CASE WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='1' ) THEN coalesce(wts.splitduration,wt.totalduration,round(si.Durationperunit / NVL(wt.splitcount, 1), 2), 0)
                                        WHEN(wt.stepinfoid IS NOT NULL AND csw.checktype ='2') THEN coalesce(wts.splitduration,wt.totalduration, round(si.Durationperunit2 / NVL(wt.splitcount, 1), 2), 0) 
                                        ELSE coalesce(wt.totalduration, wt.durationperunit, wts.splitduration, round(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0) 
                                    END employeeWorktime
                            FROM containersteptimesheet wt
                            LEFT JOIN employee e ON e.employeeid = wt.workerid
                            LEFT JOIN container c ON wt.containerid = c.containerid
                            LEFT JOIN product p ON p.productid = c.productid
                            LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                            LEFT JOIN factory f ON F.factoryid = wt.factoryid
                            LEFT JOIN workcenter w ON w.workcenterid = wt.workcenterid
                            LEFT JOIN Team t ON t.teamid = wt.teamid
                            LEFT JOIN shift SS ON ss.shiftid=t.shiftid
                            LEFT JOIN workflowstep ws ON ws.workflowstepid = wt.workflowstepid
                            LEFT JOIN stepinfo SI ON si.Stepinfoid=wt.stepinfoid
                            LEFT JOIN stepinfobase sib ON sib.stepinfobaseid=si.Stepinfobaseid
                            LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
                            LEFT JOIN containercheckinfo cki ON cki.containercheckinfoid = wt.containercheckinfoid                    
                            LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
                            LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
                            LEFT JOIN containerstepworkreport csw ON cki.containerstepworkreportid=csw.containerstepworkreportid
                            LEFT JOIN userattribute ua ON ua.parentid=c.mfgorderid 
                            LEFT JOIN enginepartplan ep ON NVL(EP.enginecontainerid, EP.containerid)=c.containerid
                            WHERE wt.workerid IS NOT NULL AND c.status='1' AND c.parentcontainerid IS NULL AND ua.userattributename='发动机' 
                                    AND ((csw.isuse = 0) OR INSTR(si.Description,'集件') > 0 OR INSTR(si.Description,'去封') > 0 )
                                ";
            #region 查询条件
            // 产品过滤
            if (para.ContainsKey("productname"))
            {
                sql += string.Format(" AND pb.productname = '{0}'", para["productname"].ToString());
            }
            // 发动机过滤
            if (para.ContainsKey("engineno"))
            {
                sql += string.Format(" AND ua.attributevalue LIKE '%{0}%' ", para["engineno"].ToString());
            }
            // 员工过滤
            if (para.ContainsKey("employeeid"))
            {
                sql += string.Format(" AND NVL(e2.employeeid,e.employeeid) ='{0}' ", para["employeeid"].ToString());
            }
            // 车间过滤
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND f.factoryid='{0}' ", para["factoryid"].ToString());
            }
            // 工区过滤
            if (para.ContainsKey("workcenterid"))
            {
                sql += string.Format(" AND w.workcenterid='{0}' ", para["workcenterid"].ToString());
            }
            // 班过滤
            if (para.ContainsKey("shiftid"))
            {
                sql += string.Format(" AND ss.shiftid='{0}' ", para["shiftid"].ToString());
            }
            //组过滤
            if (para.ContainsKey("teamid"))
            {
                sql += string.Format(" AND t.teamid='{0}' ", para["teamid"].ToString());
            }

            if (para.ContainsKey("ddlType"))
            {
                if (para["ddlType"].ToString() == "批产工时")
                {
                    sql += string.Format(" and EP.workflowstepid is not null ");
                }
                if (para["ddlType"].ToString() == "施工工时")
                {
                    sql += string.Format(" and EP.workflowstepid is null ");
                }
            }
            // 报工开始时间
            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND nvl(cki.createdate,wt.createdate) >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["startDate"].ToString());
            }
            // 报工结束时间
            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND nvl(cki.createdate,wt.createdate) <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["endDate"].ToString());
            }
            if (para.ContainsKey("status"))
            {
                if (para["status"].ToString() == "已核算")
                {
                    sql += " AND wt.AdjustDate IS NOT NULL ";
                }
                else if (para["status"].ToString() == "已核销")
                {
                    sql += " AND wt.VerificationDate IS NOT NULL ";
                }
                else
                {
                    sql += " AND NVL(wt.adjustdate,wt.verificationdate) IS NULL ";//未处理
                }
            }
            #endregion

            sql += @"
                 ) t
                GROUP BY t.employeeid,t.employeename,t.fullname
                ";
            DataTable dtQueryData = dal.GetDataTable(sql);
            return dtQueryData;
        }

        //批产、施工工时明细
        public DataTable GetEmployeeWorkTimeDetailsZS(Dictionary<string, object> para, out int pagecount, out int rowCount)
        {
            string sql = GetEmpWorkTimeSql(para);
            uMESPagingDataDTO retRt = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pagecount = Convert.ToInt32(retRt.PageCount);
            rowCount = Convert.ToInt32(retRt.RowCount);
            return retRt.DBTable;
        }

        #region 获取油封工时信息

        //油封工时查询SQL语句
        public string GetOilSealSql(Dictionary<string, object> para)
        {
            string sql = @"
                        SELECT ew.employeeid,e.employeename,e.fullname,es.containerno,'油封' specname,'' stepinfodesc,wc.workcentername,
                               es.createdate reporttime,es.createdate checkDate,os.oilsealdurationperunit durationperunit,
                               round(os.oilsealdurationperunit/NVL(ec.WorkerCount,1),2) employeeWorktime
                        FROM enginepartoilseal es
                        LEFT JOIN (
                                   SELECT u.attributevalue,MAX(u.userattributeid)
                                   FROM Userattribute u
                                   INNER JOIN mfgorder m ON m.mfgorderid = u.parentid
                                   WHERE u.userattributename = '发动机'
                                   GROUP BY u.attributevalue
                                  ) u ON es.engineno = u.attributevalue
                        LEFT JOIN containersteptimesheet csts ON csts.containercheckinfoid=es.enginepartoilsealid
                        LEFT JOIN product p ON es.productid=p.productid
                        LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                        LEFT JOIN product pc ON es.partid=pc.productid
                        LEFT JOIN oilsealcycleinfo os ON es.oilsealcycleinfoid=os.oilsealcycleinfoid
                        INNER JOIN enginepartoilsealworker ew ON es.enginepartoilsealid=ew.enginepartoilsealid
                        LEFT JOIN (SELECT ew.enginepartoilsealid,COUNT(1) WorkerCount  
                                   FROM enginepartoilsealworker ew 
                                   GROUP BY ew.enginepartoilsealid
                                  ) ec ON ec.enginepartoilsealid=es.enginepartoilsealid
                        LEFT JOIN employee e ON e.employeeid = ew.employeeid
                        LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid
                        LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid
                        LEFT JOIN factory f ON f.factoryid=wc.factoryid
                        LEFT JOIN shift SS ON ss.shiftid=e.shiftid
                        LEFT JOIN teamemployee te ON te.employeeid=e.employeeid
                        LEFT JOIN team t ON t.teamid=te.teamid
                        WHERE 1=1 ";
            #region 查询条件
            // 发动机号过滤
            if (para.ContainsKey("engineno"))
            {
                sql += string.Format(" AND es.engineno LIKE '%{0}%'", para["engineno"].ToString());
            }

            // 员工过滤
            if (para.ContainsKey("employeeid"))
            {
                sql += string.Format(" AND e.employeeid ='{0}' ", para["employeeid"].ToString());
            }

            // 车间过滤
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND f.factoryid='{0}' ", para["factoryid"].ToString());
            }

            // 工区过滤
            if (para.ContainsKey("workcenterid"))
            {
                sql += string.Format(" AND wc.workcenterid='{0}' ", para["workcenterid"].ToString());
            }

            // 班
            if (para.ContainsKey("shiftid"))
            {
                sql += string.Format(" AND ss.shiftid='{0}' ", para["shiftid"].ToString());
            }

            // 组
            if (para.ContainsKey("teamid"))
            {
                sql += string.Format(" AND t.teamid='{0}' ", para["teamid"].ToString());
            }

            // 油封创建时间
            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND  es.createdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["startDate"].ToString());
            }

            // 油封创建时间
            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND  es.createdate <= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", para["endDate"]);
            }
            #endregion
            return sql;
        }

        //油封工时汇总
        public DataTable GetOilSealWorkTimeData(Dictionary<string, object> para)
        {
            string sql = GetOilSealSql(para);
            DataTable dtOil = dal.GetDataTable(sql);
            return dtOil;
        }

        //油封工时明细
        public DataTable GetOilSealWorkTimeDataDetails(Dictionary<string, object> para, out int pagecount, out int rowCount)
        {
            string sql = GetOilSealSql(para);
            uMESPagingDataDTO retRt = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pagecount = Convert.ToInt32(retRt.PageCount);
            rowCount = Convert.ToInt32(retRt.RowCount);
            return retRt.DBTable;
        }
        #endregion

        #region 装试试车工时

        //试车工时查询sql
        public string GetShiCheWorktimeSql(Dictionary<string, object> para)
        {
            string sql = @" SELECT e.employeeid,'' containerno,st.specname,'' stepinfodesc,w.workcentername,st.completedate reporttime,'' checkDate,
                                   st.duration durationperunit,st.durationqty employeeWorktime
                            FROM tdm_steptimesheet st
                            LEFT JOIN employee e ON st.employeename = e.employeename
                            LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid
                            LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
                            LEFT JOIN teamemployee te ON te.employeeid = e.employeeid
                            LEFT JOIN team t ON t.teamid = te.teamid
                            LEFT JOIN shift s ON s.shiftid = t.shiftid
                            LEFT JOIN factory f ON f.factoryid = sv.factoryid
                            WHERE 1=1 ";
            #region 查询条件
            //发动机号
            if (para.ContainsKey("engineno"))
            {
                sql += string.Format(" AND st.engineno LIKE '%{0}%'", para["engineno"].ToString());
            }
            //机型
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND st.productfamilyname = '{0}'", para["productfamilyname"].ToString());
            }
            //分厂
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND f.factoryid = '{0}'", para["factoryid"].ToString());
            }
            //工区
            if (para.ContainsKey("workcenterid"))
            {
                sql += string.Format(" AND w.workcenterid = '{0}'", para["workcenterid"].ToString());
            }
            //班
            if (para.ContainsKey("shiftid"))
            {
                sql += string.Format(" AND s.shiftid = '{0}'", para["shiftid"].ToString());
            }
            //组
            if (para.ContainsKey("teamid"))
            {
                sql += string.Format(" AND t.teamid = '{0}'", para["teamid"].ToString());
            }
            //员工
            if (para.ContainsKey("employeeid"))
            {
                sql += string.Format(" AND e.employeeid = '{0}'", para["employeeid"].ToString());
            }
            //报工时间
            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND st.completedate >= to_date('{0}','yyyy/mm/dd hh24:mi:ss')", para["startDate"].ToString());
            }
            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND st.completedate <= to_date('{0}','yyyy/mm/dd hh24:mi:ss')", para["endDate"].ToString());
            }
            #endregion
            return sql;
        }

        //试车工时汇总
        public DataTable GetZsShiCheWorkTimeData(Dictionary<string, object> para)
        {
            string sql = @"
SELECT e.employeeid,e.employeename,e.fullname,SUM(st.durationqty) employeeworktime
FROM tdm_steptimesheet st
LEFT JOIN employee e ON st.employeename = e.employeename
LEFT JOIN sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN teamemployee te ON te.employeeid = e.employeeid
LEFT JOIN team t ON t.teamid = te.teamid
LEFT JOIN shift s ON s.shiftid = t.shiftid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
WHERE 1=1 
";
            #region 查询条件
            //发动机号
            if (para.ContainsKey("engineno"))
            {
                sql += string.Format(" AND st.engineno LIKE '%{0}%'", para["engineno"].ToString());
            }
            //机型
            if (para.ContainsKey("productfamilyname"))
            {
                sql += string.Format(" AND st.productfamilyname = '{0}'", para["productfamilyname"].ToString());
            }
            //分厂
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND f.factoryid = '{0}'", para["factoryid"].ToString());
            }
            //工区
            if (para.ContainsKey("workcenterid"))
            {
                sql += string.Format(" AND w.workcenterid = '{0}'", para["workcenterid"].ToString());
            }
            //班
            if (para.ContainsKey("shiftname"))
            {
                sql += string.Format(" AND s.shiftname = '{0}'", para["shiftname"].ToString());
            }
            //组
            if (para.ContainsKey("teamid"))
            {
                sql += string.Format(" AND t.teamid = '{0}'", para["teamid"].ToString());
            }
            //员工
            if (para.ContainsKey("employeeid"))
            {
                sql += string.Format(" AND e.employeeid = '{0}'", para["employeeid"].ToString());
            }
            //报工时间
            if (para.ContainsKey("startDate"))
            {
                sql += string.Format(" AND st.completedate >= to_date('{0}','yyyy/mm/dd hh24:mi:ss')", para["startDate"].ToString());
            }
            if (para.ContainsKey("endDate"))
            {
                sql += string.Format(" AND st.completedate <= to_date('{0}','yyyy/mm/dd hh24:mi:ss')", para["endDate"].ToString());
            }
            #endregion
            sql += @"
GROUP BY e.employeeid,e.employeename,e.fullname
            ";
            return dal.GetDataTable(sql);
        }

        //试车工时明细
        public DataTable GetZsShiCheWorkTimeDetailsData(Dictionary<string, object> para, out int pagecount, out int rowCount)
        {
            string sql = GetShiCheWorktimeSql(para);
            uMESPagingDataDTO retRt = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pagecount = Convert.ToInt32(retRt.PageCount);
            rowCount = Convert.ToInt32(retRt.RowCount);
            return retRt.DBTable;
        }
        #endregion

        //装试工时报表导出
        public DataTable ExportEmployeeWorkTimeZS(Dictionary<string, object> para)
        {
            string sql = "";
            if (para["ddlType"].ToString() == "油封工时")
            {
                sql = GetOilSealSql(para);
            }
            else if (para["ddlType"].ToString() == "试车工时")
            {
                sql = GetShiCheWorktimeSql(para);
            }
            else
            {
                sql = GetEmpWorkTimeSql(para);//批产、任务单工时
            }
            DataTable dtResult = dal.GetDataTable(sql);
            if (dtResult.Rows.Count > 0)
            {
                if (para["ddlType"].ToString() == "施工工时")
                {
                    dtResult.Columns.Remove("checktype");
                }
                if (para["ddlType"].ToString() == "批产工时")
                {
                    dtResult.Columns.Remove("wsdescription");
                }
            }
            return dtResult;
        }

        //查询装试FC工时分值属性
        public string GetWorkTimeAttribute(string factoryid)
        {
            string sql = "SELECT f.factoryid,NVL(u.attributevalue,1) attributevalue FROM  factory f LEFT JOIN Userattribute u ON u.parentid=f.factoryid ";
            sql += string.Format(" WHERE f.factoryid='{0}' AND u.userattributename='工时分值'", factoryid);
            DataTable dt = dal.GetDataTable(sql);
            if (dt.Rows.Count == 0)
            {
                return "1";
            }
            else
            {
                return dt.Rows[0]["attributevalue"].ToString();
            }

        }

        //装试FC工时导出时合并主子表
        public DataTable CombainTable(DataTable dtMain, DataTable dtChild)
        {
            DataTable dtResult = new DataTable("MainChild");

            //主表
            dtResult.Columns.Add("RN").Caption = "序号";
            dtResult.Columns.Add("fullname").Caption = "员工";
            dtResult.Columns.Add("employeename").Caption = "工号";
            dtResult.Columns.Add("employeeworktime").Caption = "个人工时";
            dtResult.Columns.Add("TotalJX").Caption = "个人绩效";

            if (dtChild.Rows.Count > 0)
            {
                //子表
                dtResult.Columns.Add("containername").Caption = "批次号";
                dtResult.Columns.Add("specname").Caption = "工序";
                dtResult.Columns.Add("stepinfodesc").Caption = "工步";

                if (dtChild.Columns.Contains("wsdescription"))
                    dtResult.Columns.Add("wsdescription").Caption = "内容描述";

                if (dtChild.Columns.Contains("workflowname"))
                    dtResult.Columns.Add("workflowname").Caption = "工艺名称";

                if (dtChild.Columns.Contains("workflowdesc"))
                    dtResult.Columns.Add("workflowdesc").Caption = "工艺描述";

                if (dtChild.Columns.Contains("checktype"))
                    dtResult.Columns.Add("checktype").Caption = "初检装";

                dtResult.Columns.Add("workcentername").Caption = "工区";
                dtResult.Columns.Add("reporttime").Caption = "报工时间";
                dtResult.Columns.Add("checkDate").Caption = "检验日期";
                dtResult.Columns.Add("durationperunit").Caption = "单位工时";
                dtResult.Columns.Add("worktime").Caption = "合计工时";
            }

            DataRow newRow;
            int count = 0;
            uMESCommonBusiness rep = new uMESCommonBusiness();
            for (int i = 0; i < dtMain.Rows.Count; i++)
            {
                if (dtChild.Rows.Count == 0)
                {
                    newRow = dtResult.NewRow();
                    //主表字段值
                    newRow["RN"] = ++count;
                    newRow["fullname"] = dtMain.Rows[i]["fullname"].ToString();
                    newRow["employeename"] = dtMain.Rows[i]["employeename"].ToString();
                    newRow["employeeworktime"] = dtMain.Rows[i]["employeeworktime"].ToString();
                    newRow["TotalJX"] = dtMain.Rows[i]["TotalJX"].ToString();
                    dtResult.Rows.Add(newRow);
                    continue;
                }

                DataRow[] row = dtChild.Select("employeeid ='" + dtMain.Rows[i]["employeeid"].ToString() + "'");
                for (int j = 0; j < row.Length; j++)
                {
                    newRow = dtResult.NewRow();
                    //主表字段值
                    newRow["RN"] = ++count;
                    newRow["fullname"] = dtMain.Rows[i]["fullname"].ToString();
                    newRow["employeename"] = dtMain.Rows[i]["employeename"].ToString();
                    newRow["employeeworktime"] = dtMain.Rows[i]["employeeworktime"].ToString();
                    newRow["TotalJX"] = dtMain.Rows[i]["TotalJX"].ToString();

                    // 子表字段值
                    newRow["containername"] = row[j]["containerno"].ToString();
                    newRow["specname"] = row[j]["specname"].ToString();
                    newRow["stepinfodesc"] = row[j]["stepinfodesc"].ToString();

                    if (dtChild.Columns.Contains("wsdescription"))
                    {
                        newRow["wsdescription"] = row[j]["wsdescription"].ToString();
                    }

                    if (dtChild.Columns.Contains("workflowname"))
                    {
                        newRow["workflowname"] = row[j]["workflowname"].ToString();
                    }

                    if (dtChild.Columns.Contains("workflowdesc"))
                    {
                        newRow["workflowdesc"] = row[j]["workflowdesc"].ToString();
                    }

                    if (dtChild.Columns.Contains("checktype"))
                    {
                        newRow["checktype"] = row[j]["checktype"].ToString();
                    }

                    newRow["workcentername"] = row[j]["workcentername"].ToString();
                    newRow["reporttime"] = row[j]["reporttime"].ToString();
                    newRow["checkDate"] = row[j]["checkDate"].ToString();
                    newRow["durationperunit"] = row[j]["durationperunit"].ToString();
                    newRow["worktime"] = row[j]["employeeworktime"].ToString();

                    dtResult.Rows.Add(newRow);
                }
            }

            return dtResult;
        }

    }
}
